package store.persistence.impl;

import store.domain.Account;
import store.persistence.AccountDao;
import store.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AccountDaoImpl implements AccountDao {
    //读取表单
    private static final String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD =
            "SELECT SIGNON.USERNAME, ACCOUNT.EMAIL, ACCOUNT.FIRSTNAME, ACCOUNT.LASTNAME, ACCOUNT.STATUS, " +
                    "ACCOUNT.ADDR1 AS address1, ACCOUNT.ADDR2 AS address2, ACCOUNT.CITY, ACCOUNT.STATE, ACCOUNT.ZIP, " +
                    "ACCOUNT.COUNTRY, ACCOUNT.PHONE, PROFILE.LANGPREF AS languagePreference, " +
                    "PROFILE.FAVCATEGORY AS favouriteCategoryId, PROFILE.MYLISTOPT AS listOption, " +
                    "PROFILE.BANNEROPT AS bannerOption, BANNERDATA.BANNERNAME " +
                    "FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA " +
                    "WHERE SIGNON.USERNAME = ? AND SIGNON.PASSWORD = ? " +
                    "AND ACCOUNT.USERID = SIGNON.USERNAME " +
                    "AND PROFILE.USERID = ACCOUNT.USERID " +
                    "AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    public static void main(String[] args) {
        AccountDao accountDao = new AccountDaoImpl();
        Account account = new Account();
        account.setUsername("5");
        account.setPassword("5");
        Account result = accountDao.getAccountByUsernameAndPassword(account);
        System.out.println("success");
    }


    public Account getAccountByUsername(String username) {
        return null;
    }

    ;

    //处理用户登录
    public Account getAccountByUsernameAndPassword(Account account) {
        Account accountResult = null;
        //通过JDBC查询对象
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            ResultSet resultSet = preparedStatement.executeQuery();
            System.out.println("查询参数: " + account.getUsername() + ", " + account.getPassword());

            //有返回记录
            if (resultSet.next()) {
                //将对象储存在accountResult中
                System.out.println("Username in DB: " + resultSet.getString("USERNAME"));
                System.out.println("Found user: " + resultSet.getString("USERNAME"));
                accountResult = this.resultSetToAccount(resultSet);
            } else {
                System.out.println("Found user: " + resultSet.getString("USERNAME"));
                System.out.println("未找到匹配的用户");
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return accountResult;
    }

    //将ResultSet中的数据转换为Account对象返回
    private Account resultSetToAccount(ResultSet resultSet) throws Exception {
        Account account = new Account();
        account.setUsername(resultSet.getString("USERNAME"));
        account.setEmail(resultSet.getString("EMAIL"));
        account.setFirstName(resultSet.getString("FIRSTNAME"));
        account.setLastName(resultSet.getString("LASTNAME"));
        account.setStatus(resultSet.getString("STATUS"));
        account.setAddress1(resultSet.getString("address1"));
        account.setAddress2(resultSet.getString("address2"));
        account.setCity(resultSet.getString("CITY"));
        account.setState(resultSet.getString("STATE"));
        account.setZip(resultSet.getString("ZIP"));
        account.setCountry(resultSet.getString("COUNTRY"));
        account.setPhone(resultSet.getString("PHONE"));
        account.setFavouriteCategoryId(resultSet.getString("favouriteCategoryId"));
        account.setLanguagePreference(resultSet.getString("languagePreference"));
        account.setListOption(resultSet.getInt("listOption") == 1);
        account.setBannerOption(resultSet.getInt("bannerOption") == 1);
        account.setBannerName(resultSet.getString("BANNERNAME"));
        return account;
    }


    private static final String INSERT_ACCOUNT_SQL = "INSERT INTO ACCOUNT (EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2, CITY, STATE, ZIP, COUNTRY, PHONE, USERID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    //注册，将Account对象插入到数据库中
    @Override
    public void insertAccount(Account account) {
        // 使用 try-with-resources 自动管理资源
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ACCOUNT_SQL)) {
            connection.setAutoCommit(false);
            preparedStatement.setString(1, account.getEmail());
            preparedStatement.setString(2, account.getFirstName());
            preparedStatement.setString(3, account.getLastName());
            preparedStatement.setString(4, account.getStatus());
            preparedStatement.setString(5, account.getAddress1());
            preparedStatement.setString(6, account.getAddress2());
            preparedStatement.setString(7, account.getCity());
            preparedStatement.setString(8, account.getState());
            preparedStatement.setString(9, account.getZip());
            preparedStatement.setString(10, account.getCountry());
            preparedStatement.setString(11, account.getPhone());
            preparedStatement.setString(12, account.getUsername());
            // 插入
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (SQLException ignored) {

        }
    }


    private static final String INSERT_PROFILE_SQL = "INSERT INTO PROFILE (LANGPREF, FAVCATEGORY, USERID) VALUES (?, ?, ?)";

    //将用户的偏好插入数据库中
    public void insertProfile(Account account) {
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_PROFILE_SQL)) {
            connection.setAutoCommit(false);
            preparedStatement.setString(1, account.getLanguagePreference());
            preparedStatement.setString(2, account.getFavouriteCategoryId());
            preparedStatement.setString(3, account.getUsername());
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (SQLException ignored) {
        }
    }

    private static final String INSERT_SIGNON_SQL = "INSERT INTO SIGNON (PASSWORD, USERNAME) VALUES (?, ?)";

    public void insertSignon(Account account) {
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SIGNON_SQL)) {

            connection.setAutoCommit(false);
            preparedStatement.setString(1, account.getPassword());
            preparedStatement.setString(2, account.getUsername());
            preparedStatement.executeUpdate();
            connection.commit();

        } catch (SQLException ignored) {

        }
    }

    ;

    private static final String UPDATE_ACCOUNT_SQL = "UPDATE ACCOUNT SET EMAIL = ?, FIRSTNAME = ?, LASTNAME = ?, STATUS = ?, ADDR1 = ?, ADDR2 = ?, CITY = ?, STATE = ?, ZIP = ?, COUNTRY = ?, PHONE = ? WHERE USERID = ?";

    public void updateAccount(Account account) {
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_ACCOUNT_SQL)) {
            connection.setAutoCommit(false);
            preparedStatement.setString(1, account.getEmail());
            preparedStatement.setString(2, account.getFirstName());
            preparedStatement.setString(3, account.getLastName());
            preparedStatement.setString(4, account.getStatus());
            preparedStatement.setString(5, account.getAddress1());
            preparedStatement.setString(6, account.getAddress2());
            preparedStatement.setString(7, account.getCity());
            preparedStatement.setString(8, account.getState());
            preparedStatement.setString(9, account.getZip());
            preparedStatement.setString(10, account.getCountry());
            preparedStatement.setString(11, account.getPhone());
            preparedStatement.setString(12, account.getUsername());
            preparedStatement.executeUpdate();
            connection.commit();

        } catch (SQLException ignored) {
        }
    }

    ;

    private static final String UPDATE_PROFILE_SQL = "UPDATE PROFILE SET LANGPREF = ?, FAVCATEGORY = ? WHERE USERID = ?";

    public void updateProfile(Account account) {
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_PROFILE_SQL)) {

            connection.setAutoCommit(false);
            preparedStatement.setString(1, account.getLanguagePreference());
            preparedStatement.setString(2, account.getFavouriteCategoryId());
            preparedStatement.setString(3, account.getUsername());
            preparedStatement.executeUpdate();
            connection.commit();

        } catch (SQLException ignored) {

        }
    }

    ;

    private static final String UPDATE_SIGNON_SQL = "UPDATE SIGNON SET PASSWORD = ? WHERE USERNAME = ?";

    public void updateSignon(Account account) {
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_SIGNON_SQL)) {

            connection.setAutoCommit(false);
            preparedStatement.setString(1, account.getPassword());
            preparedStatement.setString(2, account.getUsername());
            preparedStatement.executeUpdate();
            connection.commit();

        } catch (SQLException ignored) {
        }
    }
//        // 删除账户的SQL语句
//        private static final String DELETE_ACCOUNT_SQL = "DELETE FROM ACCOUNT WHERE USERID = ?";
//        private static final String DELETE_BANNERDATA_SQL = "DELETE FROM BANNERDATA WHERE FAVCATEGORY = (SELECT FAVCATEGORY FROM PROFILE WHERE USERID = ?)";
//        // 删除账户
//        public void deleteAccount(String username) {
//            Connection connection = null; // 在这里定义连接
//            try {
//                connection = DBUtil.getConnection();
//                connection.setAutoCommit(false);
//
//                // 删除 BANNERDATA
//                try (PreparedStatement preparedStatement = connection.prepareStatement(DELETE_BANNERDATA_SQL)) {
//                    preparedStatement.setString(1, username);
//                    preparedStatement.executeUpdate();
//                }
//
//                // 删除账户
//                try (PreparedStatement preparedStatement = connection.prepareStatement(DELETE_ACCOUNT_SQL)) {
//                    preparedStatement.setString(1, username);
//                    preparedStatement.executeUpdate();
//                }
//
//                connection.commit();
//            } catch (SQLException e) {
//                e.printStackTrace();
//                // 在出错时回滚
//                if (connection != null) {
//                    try {
//                        connection.rollback();
//                    } catch (SQLException rollbackEx) {
//                        rollbackEx.printStackTrace();
//                    }
//                }
//            } finally {
//                // 确保连接被关闭
//                if (connection != null) {
//                    try {
//                        connection.close();
//                    } catch (SQLException closeEx) {
//                        closeEx.printStackTrace();
//                    }
//                }
//            }
//        }
}
